﻿-- Function: pl_function.guardar_mpropie(character varying)

-- DROP FUNCTION IF EXISTS pl_function.guardar_mpropie(character varying);

CREATE OR REPLACE FUNCTION pl_function.guardar_mpropie(p_text character varying, p_mhresum character varying, p_vusernm character varying, p_vhostnm character varying)
  RETURNS integer AS
$BODY$
declare
	ids numeric(18, 0) := 0;
begin
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids from registro.mpropie;
	
	with new_values(  idsigma, mperson, mpredio,
			  nporcen, nestado, ctippro,
			  vhostnm, vusernm, ddatetm,
			  mhresum, nvalida
	) as (
		select  idsigma, mperson, mpredio,
			  nporcen, nestado, ctippro,
			  p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm,
			  p_mhresum mhresum, nvalida
		from pl_table.tmp_mpropie(p_text)
	), upsert as ( 
	    update registro.mpropie m set
			  mperson=n.mperson, mpredio=n.mpredio,
			  nporcen=n.nporcen, nestado=n.nestado,
			  ctippro=n.ctippro, vhostnm=p_vhostnm,
			  vusernm=p_vusernm, ddatetm=now(),
			  mhresum=p_mhresum, nvalida=n.nvalida 
	    from new_values n
	    where m.ctippro=n.ctippro and m.mhresum=n.mhresum
	    returning m.*
	)
	insert into registro.mpropie (idsigma,
			  mperson, mpredio, nporcen, nestado,
			  ctippro, vhostnm, vusernm, ddatetm,
			  mhresum, nvalida
	) select  rpad((row_number() over(order by idsigma) + ids)::varchar, 10, '0') idsigma,
			  mperson, mpredio, nporcen, nestado,
			  ctippro, vhostnm, vusernm, ddatetm,
			  mhresum, nvalida
	from new_values where not exists(select 1 from upsert where upsert.ctippro=new_values.ctippro and upsert.mhresum=new_values.mhresum);

	delete from registro.mpropie where mhresum=p_mhresum and trim(mperson)='';
	return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.guardar_mpropie(character varying)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mpropie(character varying) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mpropie(character varying) TO public;
GRANT EXECUTE ON FUNCTION pl_function.guardar_mpropie(character varying) TO desarrollo;
